const sql = require('mssql');
const baseFn = require('../../public/base.js');
const fs = require('fs'); // 图片路径
// const path = require('path'); // 图片路径
let uploadDir = './images/';

// 获取日志内容
const getLogContent = (shopList, shopInfo) => {

    let content = [];
    let nameList = {
        TM: '商品条码', CCTJ: '储存条件', CPBZ: '质量标准', GG: '商品规格', JLDW: '计量单位', JXMC: '剂型名称', JYFL: '经营范围分类',
        MC: '商品名称', PWXQ: '批文效期', PZWH: '批准文号', SCCJ: '生产厂家', TSFL: '特殊管理分类', TYMC: '通用名称', YPLX: '处方分类'
    }
    for (let ind in shopInfo) {
        if (ind in nameList && (shopInfo[ind] || shopList[ind]) && shopInfo[ind] !== shopList[ind]) {
            content.push(` 把商品编码为${shopList.BM}的商品的${nameList[ind]}从${shopInfo[ind]}改成${shopList[ind]} `);
        }
    }
    return content
}
const uploadPic = (picList) => {
    let imgList = [];
    if (0 in Object.keys(picList)) {

        for (let ind in picList) {
            let item = picList[ind];
            let newFileName = Date.now() + '_' + item.name;
            let readStream = fs.createReadStream(item.path);
            let writeSteam = fs.createWriteStream(uploadDir + newFileName);
            readStream.pipe(writeSteam);
            imgList.push(`http://www.rsimg.com/${newFileName}`);
        }

    }
    return imgList;
}

const getPicSql = (picList, keepPic) => {
    let picNameList = uploadPic(picList);
    if (0 in keepPic) {
        picNameList.push(...keepPic);
    }

    let actionSql = '';
    for (let i = 0; i < 5; i++) {
        actionSql += ` ,PWPIC${i + 1} = '${picNameList[i] ? picNameList[i] : ''}' `;
    }
    return actionSql
}

const removePicFun = (removePic) => {
    if (0 in removePic) {
        removePic.forEach(item => {
            let picName = item.slice(item.lastIndexOf("\/") + 1, item.length);
            fs.unlink(uploadDir + picName, () => {
            })
        })

    }
}

// 更新商品信息
const keepShopInfo = async (ctx) => {
    try {
        const body = ctx.request.files;
        const data = ctx.request.body;
        let userId = data.userId;
        let shopList = JSON.parse(data.shopList);
        let removePic = JSON.parse(data.removePic);
        let keepPic = JSON.parse(data.keepPic);
        removePicFun(removePic);
        // 获取商品信息
        let selectSql = ` SELECT YP.BM,YP.TM,YP.MC,YP.TYMC,YP.GG,YP.JLDW,YP.JXMC,
        YP.SCCJ,YP.PZWH,YP.PWXQ,YP.YPLX,YP.CCTJ,YP.CPBZ,YP.JYFL,YP.TSFL,YP.SSXK FROM BM_YP YP WHERE YP.BM = '${shopList.BM}' `;
        let shopInfoResult = await sql.query(selectSql);
        let shopInfo = shopInfoResult.recordset[0];
        // 更新商品信息
        let updateSql = ` UPDATE BM_YP SET TM='${shopList.TM}',CCTJ='${shopList.CCTJ}',CPBZ='${shopList.CPBZ}',GG='${shopList.GG}',JLDW='${shopList.JLDW}',JXMC='${shopList.JXMC}',
        JYFL='${shopList.JYFL}',MC='${shopList.MC}',PWXQ='${shopList.PWXQ}',PZWH='${shopList.PZWH}',SCCJ='${shopList.SCCJ}',TSFL='${shopList.TSFL}',TYMC='${shopList.TYMC}',
        YPLX='${shopList.YPLX}',SSXK='${shopList.SSXK}'${getPicSql(body, keepPic)} WHERE BM = '${shopList.BM}' `;
        let updateGSPSql = ` UPDATE GSP_FirstMedicine SET ZD9 = '${shopList.CPBZ}'  WHERE YPBM = '${shopList.BM}' `;
        let lastSql = ` SET XACT_ABORT ON Begin Tran ${updateGSPSql} ${updateSql}  Commit Tran
        `;
       
        let result = await sql.query(lastSql);
        if (result.rowsAffected[1] > 0) {
            let content = getLogContent(shopList, shopInfo);
            if (0 in content) {
                let logList = [{ userId, content: content.join(',') }];
                baseFn.createLog(logList);
            }
            ctx.body = { code: 200, message: '保存成功' }
        } else {
            ctx.body = { code: 400, message: '保存失败' }
        }
    } catch (err) {
        ctx.body = { code: 500, message: error.originalError.message };
    }
}


// 获取商品供应商信息
const getShopSupplierInfo = async (ctx) => {
    try {
        let { YPBM } = ctx.request.body;
        let selectSql = ` SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY RM.YPBM,RZ.DWBM ORDER BY RZ.RQ DESC) AS NUM,DW.MC,DW.BM,DW.LXR,
        DW.DH,RZ.RQ,DZ.JBRMC,RM.YPBM,RM.YPMC FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN BM_WLDW DW ON RZ.DWBM = DW.BM 
        LEFT JOIN DHD_ZB DZ ON RZ.ID_DHD = DZ.ID ) t WHERE t.num=1 AND t.YPBM = '${YPBM}' ORDER BY t.RQ DESC `;
        let selectResult = await sql.query(selectSql);
        if (selectResult.rowsAffected[0] > 0) {

            ctx.body = { code: 200, message: '获取成功', data: { result: selectResult.recordset } }
        } else {
            ctx.body = { code: 400, message: '获取失败' }
        }
    } catch (err) {
        ctx.body = { code: 500, message: error.originalError.message };
    }
}

//  获取所有商品信息
const getAllShopInfo = async (ctx, next) => {
    try {
        const { index, pageSize, searchList } = ctx.request.body;
        const { shopInfo, SCCJ, PZWH, JXMC, YPLX, CCTJ, YJTS, isEmpty, DWBM, GHRQ, ypbmSort } = searchList;
        let startTime = baseFn.getNowTimeId().halfDate;
        let publicSql = ` FROM BM_YP YP   LEFT JOIN ( SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM.YPBM ORDER BY DZ.RQ) AS num,
        DM.YPBM,DZ.JBRMC FROM DHD_MX DM LEFT JOIN DHD_ZB DZ ON DM.ID = DZ.ID)t where t.num = 1 ) JBR ON YP.BM = JBR.YPBM
        `;
        if (DWBM) {
            publicSql += ` LEFT JOIN ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY RM.YPBM ORDER BY RZ.RQ DESC) AS NUM,RM.YPBM,RZ.RQ,
            RZ.DWMC FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN BM_WLDW DW ON RZ.DWBM = DW.BM WHERE DW.BM = '${DWBM}' AND 
            DW.QY_FLAG = 'T') t where t.num = 1) GYS ON YP.BM = GYS.YPBM `;
        }
        // publicSql += ` WHERE YP.QY_FLAG = 'T' `;
        publicSql += ` WHERE 1=1 `;
        if (DWBM) {
            if (GHRQ[0]) {
                publicSql += ` AND GYS.RQ >= '${GHRQ[0]} 00:00:00' AND GYS.RQ <= '${GHRQ[1]} 23:59:59' `;
            }
            publicSql += ` AND GYS.YPBM IS NOT NULL `;
        }

        let totalSql = ` SELECT COUNT(YP.BM) AS total ${publicSql} `
        let selectSql = ` SELECT * FROM  ( SELECT ROW_NUMBER() OVER(ORDER BY YP.${ypbmSort === 'T' ? 'BM' : ypbmSort === 'F' ? 'BM DESC' : 'PWXQ'}) as px,YP.BM,YP.TM,YP.MC,YP.TYMC,YP.GG,YP.JLDW,YP.JXMC,
        YP.SCCJ,YP.PZWH,YP.PWXQ,YP.YPLX,YP.CCTJ,YP.CPBZ,YP.JYFL,JBR.JBRMC,ISNULL(YP.TSFL,'') AS TSFL,YP.SSXK${DWBM ? ',GYS.RQ,GYS.DWMC' : ''}
        ,YP.PWPIC1,YP.PWPIC2,YP.PWPIC3,YP.PWPIC4,YP.PWPIC5,YP.QY_FLAG AS ZT ${publicSql}  `;


        let actionSqlList = [];
        if (shopInfo) {
            actionSqlList.push(` AND (YP.BM LIKE '%${shopInfo}%' OR YP.MC LIKE '%${shopInfo}%' OR YP.JP LIKE '%${shopInfo}%' ) `);
        }
        if (isEmpty === 'YPTM') {
            actionSqlList.push(` AND ISNULL(YP.TM,'') = '' `);
        }
        if (SCCJ) {
            actionSqlList.push(` AND YP.SCCJ LIKE '%${SCCJ}%' `);
        }
        if (PZWH) {
            actionSqlList.push(` AND YP.PZWH LIKE '%${PZWH}%' `);
        }
        if (JXMC) {
            actionSqlList.push(` AND YP.JXMC = '${JXMC}' `);
        } else if (isEmpty === 'JXMC') {
            actionSqlList.push(` AND ISNULL(YP.JXMC,'') = '' `);
        }
        if (YPLX) {
            actionSqlList.push(` AND YP.YPLX = '${YPLX}' `);
        } else if (isEmpty === 'YPLX') {
            actionSqlList.push(` AND ISNULL(YP.YPLX,'') = '' `);
        }
        if (CCTJ) {
            actionSqlList.push(` AND YP.CCTJ = '${CCTJ}' `);
        } else if (isEmpty === 'CCTJ') {
            actionSqlList.push(` AND ISNULL(YP.CCTJ,'') = '' `);
        }
        if (isEmpty === 'PWXQ') {
            actionSqlList.push(` AND ISNULL(YP.PWXQ,'') = '' `);
        }
        if (YJTS) {

            let endTime = baseFn.getNowTimeId(baseFn.getDayAgo(YJTS, 'after')).halfDate;

            actionSqlList.push(` AND YP.PWXQ <= '${endTime} 23:59:59' AND YP.PWXQ >= '${startTime} 00:00:00' `);
        } else if (YJTS === 0) {
            actionSqlList.push(` AND YP.PWXQ <= '${startTime} 23:59:59'  `);
        }

        totalSql += actionSqlList.join(' ');
        selectSql += ` ${actionSqlList.join(' ')} ) t1 WHERE T1.px between ${(index - 1) * pageSize + 1} and ${index * pageSize} `;
        let totalResult = await sql.query(totalSql);
        let selectResult = await sql.query(selectSql);
        selectResult.recordset.forEach(item => {
            if (item.PIC1) {
                item.PIC1 = item.PIC1.toString();
            }
        });
        ctx.body = { code: 200, message: '获取数据成功', data: { total: totalResult.recordset[0].total, result: selectResult.recordset } }

    } catch (err) {
        ctx.body = { code: 500, message: '获取数据成功', err }

    }
}


// 获取所有供应商信息
const getAllSupplierInfo = async (ctx) => {
    try {

        let selectSql = ` SELECT DW.BM,DW.MC,DW.JP FROM BM_WLDW DW WHERE DWLX = 1 `;
        let selectResult = await sql.query(selectSql);


        ctx.body = { code: 200, message: '获取成功', data: { result: selectResult.recordset } }

    } catch (err) {
        ctx.body = { code: 500, message: error.originalError.message };
    }
}

// 获取要导出的商品信息
const getExportShopInfoData = async (ctx) => {
    try {
        const { searchList } = ctx.request.body;
        const { shopInfo, SCCJ, PZWH, JXMC, YPLX, CCTJ, YJTS, isEmpty, DWBM, GHRQ } = searchList;
        let startTime = baseFn.getNowTimeId().halfDate;
        let publicSql = ` FROM BM_YP YP LEFT JOIN ( SELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY DM.YPBM ORDER BY DZ.RQ) AS num,
        DM.YPBM,DZ.JBRMC FROM DHD_MX DM LEFT JOIN DHD_ZB DZ ON DM.ID = DZ.ID)t where t.num = 1 ) JBR ON YP.BM = JBR.YPBM  `;
        if (DWBM) {
            publicSql += ` LEFT JOIN ( SELECT * FROM ( SELECT ROW_NUMBER() OVER(PARTITION BY RM.YPBM ORDER BY RZ.RQ DESC) AS NUM,RM.YPBM,RZ.RQ,RZ.DWMC 
            FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN BM_WLDW DW ON RZ.DWBM = DW.BM WHERE DW.BM = '${DWBM}' AND DW.QY_FLAG = 'T') t where t.num = 1) GYS ON YP.BM = GYS.YPBM `;
        }
        publicSql += ` WHERE YP.QY_FLAG = 'T' `;
        if (DWBM) {
            if (GHRQ[0]) {
                publicSql += ` AND GYS.RQ >= '${GHRQ[0]} 00:00:00' AND GYS.RQ <= '${GHRQ[1]} 23:59:59' `;
            }
            publicSql += ` AND GYS.YPBM IS NOT NULL `;
        }


        let selectSql = `  SELECT ROW_NUMBER() OVER(ORDER BY YP.PWXQ) as px,YP.BM,YP.TM,YP.MC,YP.TYMC,YP.GG,YP.JLDW,YP.JXMC,JBR.JBRMC,
        YP.SCCJ,YP.PZWH,YP.PWXQ,YP.YPLX,YP.CCTJ,YP.CPBZ,YP.JYFL,ISNULL(YP.TSFL,'') AS TSFL,YP.SSXK${DWBM ? ',GYS.RQ,GYS.DWMC' : ''} ${publicSql}  `;
        let actionSqlList = [];
        if (shopInfo) {
            actionSqlList.push(` AND (YP.BM LIKE '%${shopInfo}%' OR YP.MC LIKE '%${shopInfo}%' OR YP.JP LIKE '%${shopInfo}%' ) `);
        }
        if (isEmpty === 'YPTM') {
            actionSqlList.push(` AND ISNULL(YP.TM,'') = '' `);
        }
        if (SCCJ) {
            actionSqlList.push(` AND YP.SCCJ LIKE '%${SCCJ}%' `);
        }
        if (PZWH) {
            actionSqlList.push(` AND YP.PZWH LIKE '%${PZWH}%' `);
        }
        if (JXMC) {
            actionSqlList.push(` AND YP.JXMC = '${JXMC}' `);
        } else if (isEmpty === 'JXMC') {
            actionSqlList.push(` AND ISNULL(YP.JXMC,'') = '' `);
        }
        if (YPLX) {
            actionSqlList.push(` AND YP.YPLX = '${YPLX}' `);
        } else if (isEmpty === 'YPLX') {
            actionSqlList.push(` AND ISNULL(YP.YPLX,'') = '' `);
        }
        if (CCTJ) {
            actionSqlList.push(` AND YP.CCTJ = '${CCTJ}' `);
        } else if (isEmpty === 'CCTJ') {
            actionSqlList.push(` AND ISNULL(YP.CCTJ,'') = '' `);
        }
        if (YJTS) {

            let endTime = baseFn.getNowTimeId(baseFn.getDayAgo(YJTS, 'after')).halfDate;

            actionSqlList.push(` AND YP.PWXQ <= '${endTime} 23:59:59' AND YP.PWXQ >= '${startTime} 00:00:00' `);
        } else if (YJTS === 0) {
            actionSqlList.push(` AND YP.PWXQ <= '${startTime} 23:59:59'  `);
        }

        selectSql += ` ${actionSqlList.join(' ')}  `;
        let selectResult = await sql.query(selectSql);
        ctx.body = { code: 200, message: '获取数据成功', data: { result: selectResult.recordset } }

    } catch (err) {
        ctx.body = { code: 500, message: '获取数据成功', err }

    }
}

// 批量改变商品的状态
const changeShopInfoStatus = async (ctx) => {
    try {
        let { ypbmList, status } = ctx.request.body;
        let ypbmStr = ypbmList.map(item => `'${item}'`).join(',');
        let actionSql = ` update BM_YP  set QY_FLAG = '${status}' where BM IN (${ypbmStr}) `;
        let result = await sql.query(actionSql);
        if (result.rowsAffected[0] > 0) {
            ctx.body = { code: 200, message: '保存成功' }
        } else {
            ctx.body = { code: 400, message: '保存失败' }
        }
    } catch (err) {
        ctx.body = { code: 500, err }
    }

}


module.exports = {
    'POST /keepShopInfo': keepShopInfo,
    'POST /getAllSupplierInfo': getAllSupplierInfo,
    'POST /getShopSupplierInfo': getShopSupplierInfo,
    'POST /getExportShopInfoData': getExportShopInfoData,
    'POST /changeShopInfoStatus': changeShopInfoStatus,
    'POST /getAllShopInfo': getAllShopInfo
}
